﻿using Anke.SHManage.IDAL;
using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public class DriverReportDAL
    {
        IDALContext dalContext = new DALContextFactory().GetDALContext();
        AKV7ManageEntities db = DBContextFactory.GetDBContext() as AKV7ManageEntities;


        #region 取出司机出车任务列表（用来勾选）
        public object GetDriverReportForPick(ref int rowtotal, DateTime start, DateTime end, string centerID, string stationID, string workID, string ambulanceCode, string eventDispatchCenterID)
        {
            StringBuilder sb = new StringBuilder();
     
            SqlConnectionStringBuilder buildermain = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);
            sb.Append(@"select distinct TaskCode
                                    --,isnull(sum(PaidEmergencyFee),0) as 院前急救费,isnull(sum(PaidDrugFee),0) as 药费
                                    --,isnull(sum(PaidConsumableFee),0) as 材料费,isnull(sum(PaidExamineFee),0) as 检查费,isnull(sum(PaidMeasureFee),0) as 治疗费
                                    ,isnull(sum(PaidPRFee),0) as 医疗费合计,isnull(sum(PaidCarFee),0) as 车费
                                    ,isnull(sum(ReceivableTotal),0) as 应收,isnull(sum(PaidMoney),0) as 实收 
                                    into #chargetemp
                from ").Append(buildermain.InitialCatalog).Append(".dbo.M_PatientCharge mpc left join TTask tt on mpc.TaskCode = tt.任务编码  where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("tt.生成任务时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("tt.生成任务时刻", end, sb);
            WhereClauseUtility.AddStringLike("dbo.GetWorkIDStr(tt.任务编码,3)", workID, sb);
            sb.Append(@"group by TaskCode 

                       select TaskCode
                       ,sum(case when (ChargeType=0) then isnull(PaidMoney,0) else 0 end) as 现金收费
                       ,sum(case when (ChargeType in (4,5,10,11)) then isnull(PaidMoney,0) else 0 end) as 医保收费
                       ,sum(case when (ChargeType=7) then isnull(PaidMoney,0) else 0 end) as 分币误差  
                       ,sum(case when (ChargeType=1) then isnull(PaidMoney,0) else 0 end) as 支付宝实收
                       ,sum(case when (ChargeType=2) then isnull(PaidMoney,0) else 0 end) as 微信实收
                       ,sum(case when (ChargeType in (3,8)) then isnull(PaidMoney,0) else 0 end) as 银联收费
                       ,sum(case when (ChargeType=6) then isnull(PaidMoney,0) else 0 end) as 离休合计
                       into #detailtemp 
                       from ").Append(buildermain.InitialCatalog).Append(@".dbo.M_PatientChargeDetail mpcd 
                       left join TTask tt on mpcd.TaskCode = tt.任务编码  
                       where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("tt.生成任务时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("tt.生成任务时刻", end, sb);
            WhereClauseUtility.AddStringLike("dbo.GetWorkIDStr(tt.任务编码,3)", workID, sb);
            sb.Append(@" group by TaskCode ");
            sb.Append(@"
                select 任务编码=tt.任务编码,任务日期=CONVERT(varchar(10),tt.生成任务时刻,120),分站=ts.名称,
                实际标识=ta.实际标识,车辆编码=tt.车辆编码,呼救电话=tace.呼救电话,
                联系电话=tace.联系电话,联系人=tace.联系人,现场地址=tace.现场地址,患者姓名=tace.患者姓名,性别=tace.性别,
	            年龄=tace.年龄,主诉=tace.主诉,送往地点=tace.送往地点,生成任务时刻=tt.生成任务时刻,调度员工号=tp.工号,
	            调度员姓名=tp.姓名,
	            isnull(dbo.GetStr(tt.任务编码,3),'') as 司机,isnull(dbo.GetWorkIDStr(tt.任务编码,3),'') as 司机工号
                ,isnull(dbo.GetStr(tt.任务编码,4),'') as 医生 ,isnull(dbo.GetWorkIDStr(tt.任务编码,4),'') as 医生工号
                ,isnull(dbo.GetStr(tt.任务编码,5),'') as 护士 ,isnull(dbo.GetWorkIDStr(tt.任务编码,5),'') as 护士工号
                ,isnull(dbo.GetStr(tt.任务编码,6),'') as 担架员 ,isnull(dbo.GetWorkIDStr(tt.任务编码,6),'') as 担架员工号,
	            出车时刻=tt.出车时刻,完成任务时刻=tt.完成时刻 
                --,isnull(ct.院前急救费,0) as 院前急救费,isnull(ct.药费,0) as 药费,isnull(ct.材料费,0) as 材料费
                --,isnull(ct.检查费,0) as 检查费,isnull(ct.治疗费,0) as 治疗费
                ,isnull(ct.医疗费合计,0) as 医疗费合计,isnull(ct.车费,0) as 车费
                ,isnull(ct.应收,0) as 应收,isnull(ct.实收,0) as 实收
                ,isnull(dt.现金收费,0) as 现金收费,isnull(dt.医保收费,0) as 医保收费,isnull(dt.分币误差,0) as 分币误差
                ,isnull(dt.支付宝实收,0) as 支付宝实收,isnull(dt.微信实收,0) as 微信实收
                ,isnull(dt.银联收费,0) as 银联收费,isnull(dt.离休合计,0) as 离休合计  
                 into #temp
                 from TTask  tt
                 left join TAcceptEvent tace on tace.事件编码=tt.事件编码 and tace.受理序号=tt.受理序号
                 left join TAlarmEvent tae on tae.事件编码=tt.事件编码
                 left join TAmbulance ta on ta.车辆编码=tt.车辆编码 
                 left join TPerson tp on tp.编码=tt.责任调度人编码
                 left join TStation ts on ts.编码=tt.分站编码
                 left join #chargetemp ct on tt.任务编码 = ct.TaskCode  
                 left join #detailtemp dt on tt.任务编码 = dt.TaskCode  
                 where 1=1 and (dbo.GetWorkIDStr(tt.任务编码,3) = '" + workID + "' or dbo.GetWorkIDStr(tt.任务编码,6) = '" + workID + "') ");
            WhereClauseUtility.AddDateTimeGreaterThan("tt.生成任务时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("tt.生成任务时刻", end, sb);
            WhereClauseUtility.AddInSelectQuery("ts.中心编码", centerID, sb);
            WhereClauseUtility.AddInSelectQuery("ts.编码", stationID, sb);
            //WhereClauseUtility.AddStringEqual("dbo.GetWorkIDStr(tt.任务编码,3)", workID, sb);
            WhereClauseUtility.AddStringEqual("tt.车辆编码", ambulanceCode, sb);
            //WhereClauseUtility.AddStringEqual("tae.中心编码", eventDispatchCenterID, sb); //2020-11-22 zch

            //判断 任务编码不在 司机日报表从表里的任务编码中
            sb.Append(@"  and tt.任务编码 not in  (select taskCode    ");
            sb.Append(@" from ").Append(buildermain.InitialCatalog).Append(".dbo.D_DriverReportPick drp ");
            sb.Append(@"where  drp.StartTaskTime >='" + start + "' and  drp.StartTaskTime <'" + end + "' ) ");
            sb.Append(@"group by tt.任务编码,tt.生成任务时刻,ts.名称,ta.实际标识,tt.车辆编码,tace.呼救电话,tace.联系电话,tace.联系人,tace.现场地址,tace.患者姓名,tace.性别,tace.年龄,tace.主诉,tace.送往地点,tt.生成任务时刻,tp.工号,tp.姓名,tt.出车时刻,tt.完成时刻
                                --,ct.院前急救费,ct.药费,ct.材料费,ct.检查费,ct.治疗费
                                ,ct.医疗费合计,ct.车费,ct.应收,ct.实收,dt.现金收费,dt.医保收费,dt.分币误差
                                ,dt.支付宝实收,dt.微信实收,dt.银联收费,dt.离休合计 ");

            sb.Append(@"SELECT * FROM #temp t order by  分站,生成任务时刻  ");
            sb.Append(@"SELECT count(*) FROM #temp t ");
            sb.Append("drop table #temp,#chargetemp,#detailtemp ");

//            //2021-05-27 plq 参考郭工方法取消标量值函数，调整SQL
//            StringBuilder sb = new StringBuilder();
//            sb.Append("declare @BeginTime datetime ");
//            sb.Append("declare @EndTime datetime  ");
//            sb.Append("set @BeginTime='" + start + "' ");
//            sb.Append("set @EndTime='" + end + "' ");
//            sb.Append(@"
//                    declare @EventCodeB char(16) 
//                    declare @EventCodeE char(16) 
//                    declare @TaskCodeB  char(20) 
//                    declare @TaskCodeE  char(20) 
//                    set @EventCodeB = convert(char(8),@BeginTime,112)+'00000000' 
//                    set @EventCodeE = convert(char(8),dateadd(day,1,@EndTime),112)+'00000000' 
//                    set @TaskCodeB = @EventCodeB+'0000' 
//                    set @TaskCodeE = @EventCodeE+'0000' 
//             ");
//            sb.Append(@"
//                    select distinct 任务编码,人员类型编码,a.姓名,tp.工号  
//                    into #temp
//                    from TTaskPersonLink a 
//                    left join tperson tp on a.人员编码 = tp.编码 
//                    where a.任务编码>@TaskCodeB and a.任务编码<=@TaskCodeE ");
//            sb.Append(@"
//                    select distinct 
//                    任务编码 as 任务编码,
//
//                    (select  a.工号+','   
//                    from #temp a 
//                    where a.任务编码>@TaskCodeB and a.任务编码<=@TaskCodeE
//                    and a.任务编码=t.任务编码
//                    and a.人员类型编码=3  FOR XML PATH('')) as 司机工号
//                    ,
//                    (select  a.工号+','   
//                    from #temp a 
//                    where a.任务编码>@TaskCodeB and a.任务编码<=@TaskCodeE
//                    and a.任务编码=t.任务编码
//                    and a.人员类型编码=4  FOR XML PATH('')) as 医生工号
//                    ,
//                    (select  a.工号+','   
//                    from #temp a 
//                    where a.任务编码>@TaskCodeB and a.任务编码<=@TaskCodeE
//                    and a.任务编码=t.任务编码
//                    and a.人员类型编码=5  FOR XML PATH('')) as 护士工号
//                    ,
//                    (select  a.工号+','   
//                    from #temp a 
//                    where a.任务编码>@TaskCodeB and a.任务编码<=@TaskCodeE
//                    and a.任务编码=t.任务编码
//                    and a.人员类型编码=6  FOR XML PATH('')) as 担架工工号
//                    into #temp1
//                    from TTaskPersonLink t
//                    where t.任务编码>@TaskCodeB and t.任务编码<=@TaskCodeE
//                    and t.人员类型编码=3
//                    -- order by 任务编码
//            ");
//            sb.Append(@"
//                            select 任务编码=tt.任务编码,任务日期=CONVERT(varchar(10),tt.生成任务时刻,120),分站=ts.名称,
//                            实际标识=ta.实际标识,车辆编码=tt.车辆编码,呼救电话=tace.呼救电话,
//                            联系电话=tace.联系电话,联系人=tace.联系人,现场地址=tace.现场地址,患者姓名=tace.患者姓名,性别=tace.性别,
//            	            年龄=tace.年龄,主诉=tace.主诉,送往地点=tace.送往地点,生成任务时刻=tt.生成任务时刻
//                            ,调度员工号=tp.工号,调度员姓名=tp.姓名
//                            ,司机=tt.司机,司机工号=left(t1.司机工号,case when len(t1.司机工号)-1>0 then len(t1.司机工号)-1 else 0 end)
//                            ,医生=tt.医生 ,医生工号=left(t1.医生工号,case when len(t1.医生工号)-1>0 then len(t1.医生工号)-1 else 0 end)
//                            ,护士=tt.护士 ,护士工号=left(t1.护士工号,case when len(t1.护士工号)-1>0 then len(t1.护士工号)-1 else 0 end)
//                            ,担架员=tt.担架工 ,担架员工号=left(t1.担架工工号,case when len(t1.担架工工号)-1>0 then len(t1.担架工工号)-1 else 0 end)
//            	            ,出车时刻=tt.出车时刻,完成任务时刻=tt.完成时刻  
//                             into #temp5
//                             from TTask  tt
//                             left join TAcceptEvent tace on tace.事件编码=tt.事件编码 and tace.受理序号=tt.受理序号
//                             left join TAlarmEvent tae on tae.事件编码=tt.事件编码
//                             left join TAmbulance ta on ta.车辆编码=tt.车辆编码 
//                             left join TPerson tp on tp.编码=tt.责任调度人编码
//                             left join TStation ts on ts.编码=tt.分站编码
//                             left join #temp1  t1  on t1.任务编码 = tt.任务编码
//                             where 1=1 ");
//            sb.Append(@"
//                    and tt.任务编码>@TaskCodeB and tt.任务编码<=@TaskCodeE ");
//            WhereClauseUtility.AddDateTimeGreaterThan("tt.生成任务时刻", start, sb);
//            WhereClauseUtility.AddDateTimeLessThan("tt.生成任务时刻", end, sb);
//            WhereClauseUtility.AddInSelectQuery("ts.中心编码", centerID, sb);
//            WhereClauseUtility.AddInSelectQuery("ts.编码", stationID, sb);
//            WhereClauseUtility.AddStringEqual("tt.车辆编码", ambulanceCode, sb);

//            //判断 任务编码不在 司机日报表从表里的任务编码中
//            SqlConnectionStringBuilder buildermain = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);
//            sb.Append(@"  and tt.任务编码 not in  (select taskCode    ");
//            sb.Append(@" from ").Append(buildermain.InitialCatalog).Append(".dbo.D_DriverReportPick drp ");
//            sb.Append(@"where  drp.StartTaskTime >='" + start + "' and  drp.StartTaskTime <'" + end + "' ) ");
//            sb.Append(@"
//                SELECT 任务编码,任务日期,分站,
//                实际标识,车辆编码,呼救电话,
//                联系电话,联系人,现场地址,患者姓名,性别,
//                年龄,主诉,送往地点,生成任务时刻,调度员工号,
//                调度员姓名,
//                司机,司机工号,
//                医生,医生工号,
//                护士,护士工号,
//                担架员,担架员工号,
//                出车时刻,完成任务时刻 
//                into #tempA
//                FROM #temp5 t 
//                where 1=1 ");
//            sb.Append(@"and (司机工号= '" + workID + "' or 担架员工号= '" + workID + "') ");
//            sb.Append(@"
//                order by  分站,生成任务时刻  
//
//                select * from #tempA
//                SELECT count(*) FROM #tempA 
//                drop table #temp,#temp1  ,#temp5,#tempA
//                ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            List<DriverReportInfo> list = new List<DriverReportInfo>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                DriverReportInfo info = new DriverReportInfo();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new DriverReportInfo();
                    info.TaskCode = Convert.ToString(dr["任务编码"]);
                    info.TaskDate = Convert.ToString(dr["任务日期"]);
                    info.DispatcherName = Convert.ToString(dr["调度员姓名"]);
                    info.DispatcherWorkID = Convert.ToString(dr["调度员工号"]);
                    info.StationName = Convert.ToString(dr["分站"]);
                    info.AmbulanceCode = dr["车辆编码"].ToString();
                    info.RealAmbulanceSign = Convert.ToString(dr["实际标识"]);
                    info.AlarmTel = dr["呼救电话"].ToString();
                    info.LinkMan = Convert.ToString(dr["联系人"]);
                    info.LinkCall = Convert.ToString(dr["联系电话"]);
                    info.PatientName = dr["患者姓名"].ToString();
                    info.Sex = dr["性别"].ToString();
                    info.Age = dr["年龄"].ToString();
                    info.AlarmReason = dr["主诉"].ToString();
                    info.LocalAddr = Convert.ToString(dr["现场地址"]);
                    info.SendAddr = Convert.ToString(dr["送往地点"]);
                    info.StartTaskTime = Convert.ToDateTime(dr["生成任务时刻"].ToString());
                    info.Driver = Convert.ToString(dr["司机"]);
                    info.DriverWorkID = Convert.ToString(dr["司机工号"]);
                    info.Doctor = Convert.ToString(dr["医生"]);
                    info.DoctorWorkID = Convert.ToString(dr["医生工号"]);
                    info.Nurse = Convert.ToString(dr["护士"]);
                    info.NurseWorkID = Convert.ToString(dr["护士工号"]);
                    info.Stretcher = Convert.ToString(dr["担架员"]);
                    info.StretcherWorkID = Convert.ToString(dr["担架员工号"]);
                    info.DrivingTime = Convert.ToString(dr["出车时刻"]);
                    info.CompletedTime = Convert.ToString(dr["完成任务时刻"]);

                    //info.EmergencyFee = Convert.ToString(dr["院前急救费"]); //add2021-10-15 plq 
                    //info.DrugFee = Convert.ToString(dr["药费"]);
                    //info.ConsumableFee = Convert.ToString(dr["材料费"]);
                    //info.ExamineFee = Convert.ToString(dr["检查费"]);
                    //info.MeasureFee = Convert.ToString(dr["治疗费"]);
                    info.PaidPRFee = Convert.ToString(dr["医疗费合计"]); //add2021-10-15 plq 
                    info.PaidCarFee = Convert.ToString(dr["车费"]);
                    info.ReceivableTotal = Convert.ToString(dr["应收"]);
                    info.PaidMoney = Convert.ToString(dr["实收"]);
                    info.XJMoney = Convert.ToString(dr["现金收费"]); //add2021-10-15 plq 医保上线移植
                    info.YBMoney = Convert.ToString(dr["医保收费"]);
                    info.MLMoney = Convert.ToString(dr["分币误差"]);

                    info.ZFBMoney = Convert.ToString(dr["支付宝实收"]); //add2022-01-07 plq 新增支付宝、微信、银联收费、离休明细列
                    info.WXMoney = Convert.ToString(dr["微信实收"]);
                    info.YLMoney = Convert.ToString(dr["银联收费"]); //add2022-01-13 plq 银联收费
                    info.LXMoney = Convert.ToString(dr["离休合计"]);

                    list.Add(info);
                }
            }

            else
            {
                list = null;
                rowtotal = 0;
                return list;
            }
            rowtotal = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return list;
        }
        #endregion

        #region 获取combobox的值
        //获取车辆实际标识
        public object Get_ActualCodeList(DateTime start, DateTime end, string workID, string eventDispatchCenterID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct 车辆编码=tt.车辆编码,实际标识=ta.实际标识
                        from TTask tt
                        left join TAmbulance ta on ta.车辆编码=tt.车辆编码
                        left join TTaskPersonLink ttpl on ttpl.任务编码=tt.任务编码
                        left join TPerson tp on tp.编码=ttpl.人员编码
                        left join TAlarmEvent tae on tae.事件编码=tt.事件编码
                        where  1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("生成任务时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("生成任务时刻", end, sb);
            WhereClauseUtility.AddStringEqual("tp.工号", workID, sb);
            //WhereClauseUtility.AddStringEqual("tae.中心编码", eventDispatchCenterID, sb); //2020-11-22 zch
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["车辆编码"].ToString();
                    info.Name = dr["实际标识"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //取根据登录人工号取司机姓名，工号拼在一起
        public object Get_DriverList(DateTime start, DateTime end, string workID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct  dbo.GetNameWorkStr(tt.任务编码,3) as 司机
                        from TTask tt                      
                        where  1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("生成任务时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("生成任务时刻", end, sb);
            WhereClauseUtility.AddStringEqual("GetNameWorkStr(tt.任务编码,3)", workID, sb);
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["司机"].ToString();
                    info.Name = dr["司机"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //取根据登录人工号取医生姓名，工号拼在一起
        public object Get_DoctorList(DateTime start, DateTime end, string workID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct dbo.GetNameWorkStr(tt.任务编码,4) as 医生
                        from TTask tt                      
                        where  1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("生成任务时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("生成任务时刻", end, sb);
            WhereClauseUtility.AddStringEqual("GetNameWorkStr(tt.任务编码,4)", workID, sb);
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["医生"].ToString();
                    info.Name = dr["医生"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //取根据登录人工号取护士姓名，工号拼在一起
        public object Get_NurseList(DateTime start, DateTime end, string workID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct  dbo.GetNameWorkStr(tt.任务编码,5) as 护士
                        from TTask tt                      
                        where  1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("生成任务时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("生成任务时刻", end, sb);
            WhereClauseUtility.AddStringEqual("GetNameWorkStr(tt.任务编码,5)", workID, sb);
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["护士"].ToString();
                    info.Name = dr["护士"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //取根据登录人工号取担架员姓名，工号拼在一起
        public object Get_StretcherList(DateTime start, DateTime end, string workID)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct dbo.GetNameWorkStr(tt.任务编码,6) as 担架员
                        from TTask tt                      
                        where  1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("生成任务时刻", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("生成任务时刻", end, sb);
            WhereClauseUtility.AddStringEqual("GetNameWorkStr(tt.任务编码,6)", workID, sb);
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["担架员"].ToString();
                    info.Name = dr["担架员"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //取分站长所属分站的车辆
        public object GetStationAmbulance(DateTime start, DateTime end, string stationCode)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct 车辆编码=AmbulanceCode,实际标识=AmbulanceNumber
                        from D_DriverReport                       
                        where  1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("ReportDate", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("ReportDate", end, sb);
            WhereClauseUtility.AddStringEqual("StationCode", stationCode, sb);
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["车辆编码"].ToString();
                    info.Name = dr["实际标识"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        #endregion

        #region  取司机日报表填写页面相关数据，赋值到日报填写页面上
        //取车辆相关信息   
        public D_DriverReport GetAmbulanceLink(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" select distinct 车辆编码=ta.车辆编码,实际标识=ta.实际标识,分站=ts.名称,分站编码=ts.编码,中心编码=ts.中心编码
                                       ,填写人工号=dr.AgentWorkID,报表日期=dr.ReportDate,班次=dr.BanCi,出车数=dr.TaskTimes, 司机 =drp.Driver +'('+drp.DriverWorkID+')'
                        from D_DriverReportPick drp
                        left join D_DriverReport dr on dr.ID=drp.ReportID ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TTask tt on tt.任务编码=drp.taskCode ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=tt.分站编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TAmbulance ta on ta.车辆编码=tt.车辆编码");
            sb.Append(" where drp.ReportID='" + id + "'  ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                D_DriverReport info = new D_DriverReport();
                if (dr.Read())
                {
                    info.AmbulanceCode = dr["车辆编码"].ToString();
                    info.AmbulanceNumber = dr["实际标识"].ToString();
                    info.AgentWorkID = dr["填写人工号"].ToString();
                    info.StationName = dr["分站"].ToString();
                    info.StationCode = dr["分站编码"].ToString();
                    info.CenterCode = Convert.ToInt32(dr["中心编码"]);
                    info.BanCi = dr["班次"].ToString();
                    info.TaskTimes = dr["出车数"].ToString();
                    info.ReportDate = Convert.ToDateTime(dr["报表日期"]);
                    info.DriverName = dr["司机"].ToString();

                }
                return info;
            }
        }

        //取收费相关信息   
        public D_DriverReport GetFeeLink(string id)
        {
            StringBuilder sb = new StringBuilder();
            //2016-6-15 空车数：取值病历填写界面内空车版本的车到人走、车到不要（未诊治）、突发事件三项的已提交病例数合计，途中召回和其他不计入此统计表空车数
            sb.Append(@" 
                  select pcd.TaskCode,pcd.PatientOrder,
                  现金收费=sum(case when (pcd.ChargeType=0) then isnull(pcd.PaidMoney,0) else 0 end)
	              ,医保收费=sum(case when (pcd.ChargeType in (4,5,10,11)) then isnull(pcd.PaidMoney,0) else 0 end)
	              ,分币误差=sum(case when (pcd.ChargeType=7) then isnull(pcd.PaidMoney,0) else 0 end)
                  ,支付宝实收=sum(case when (pcd.ChargeType=1) then isnull(pcd.PaidMoney,0) else 0 end)
                  ,微信实收=sum(case when (pcd.ChargeType=2) then isnull(pcd.PaidMoney,0) else 0 end)
                  ,银联收费=sum(case when (pcd.ChargeType in (3,8)) then isnull(pcd.PaidMoney,0) else 0 end)
                  ,离休合计=sum(case when (pcd.ChargeType=6) then isnull(pcd.PaidMoney,0) else 0 end)
	              into #temp 
	              from D_DriverReport dr 
	              left join D_DriverReportPick drp on  dr.ID=drp.ReportID
	              left join M_PatientCharge mpc on mpc.TaskCode = drp.taskCode
	              left join M_PatientChargeDetail pcd on mpc.TaskCode=pcd.TaskCode and mpc.PatientOrder=pcd.PatientOrder
                  where dr.ID='" + id + "' "); //add2021-10-15 plq 医保上线移植
            sb.Append(@" 
                  group by pcd.TaskCode,pcd.PatientOrder ");
            sb.Append(@" 
                  select     
                  空车=sum(case when pr.PatientVersion='空车' and pr.EmptyReason<>'途中召回'  and  pr.EmptyReason<>'其他' then 1 else 0 end),	
                  人数=(select count(TaskCode) from M_PatientRecord where TaskCode in (select taskCode from D_DriverReportPick where ReportID='" + id + @"') and PatientVersion!='空车'),   
                  --发票份数=(select count(TaskCode) from M_PatientChargeLog where TaskCode in (select taskCode from D_DriverReportPick where ReportID='" + id + @"')), 
                  发票份数=(select count(TaskCode) from M_PatientCharge where TaskCode in (select taskCode from D_DriverReportPick where ReportID='" + id + @"')), --修改发票数量，2017-10-23 周志猛
	              摆渡费=isnull(sum(mpc.baidufei),0),
	              车费=isnull(sum(mpc.CarFee),0),
	              等候费=isnull(sum(mpc.WaitingFee),0),
	              急救费=isnull(sum(mpc.EmergencyFee),0),
	              药品费=isnull(sum(mpc.DrugFeeTotal),0),检查费=isnull(sum(mpc.ExamineFeeTotal),0),
	              材料费=isnull(sum(mpc.ConsumableFeeTotal),0),救治措施费=isnull(sum(mpc.MeasureFeeTotal),0),
	              治疗费=isnull(sum(mpc.DrugFeeTotal),0)+isnull(sum(mpc.ExamineFeeTotal),0)+isnull(sum(mpc.ConsumableFeeTotal),0)+isnull(sum(mpc.MeasureFeeTotal),0)+isnull(sum(mpc.EmergencyFee),0),
	              应收合计=isnull(sum(mpc.ReceivableTotal),0),实收合计=isnull(sum(mpc.PaidMoney),0)
                  ,车费实收合计=isnull(sum(mpc.PaidCarFee),0),
                  治疗费实收合计 = isnull(sum(mpc.PaidPRFee),0)
                  ,现金收费=isnull(sum(t.现金收费),0)
                  ,医保收费=isnull(sum(t.医保收费),0)
                  ,分币误差=isnull(sum(t.分币误差),0)
                  ,支付宝实收=isnull(sum(t.支付宝实收),0)
                  ,微信实收=isnull(sum(t.微信实收),0)
                  ,银联收费=isnull(sum(t.银联收费),0)
                  ,离休合计=isnull(sum(t.离休合计),0)
	  
	              from D_DriverReport dr 
	              left join D_DriverReportPick drp on  dr.ID=drp.ReportID
	              --left join M_PatientRecord pr on  pr.TaskCode=drp.taskCode
	              left join M_PatientCharge mpc on mpc.TaskCode = drp.taskCode
                  left join M_PatientRecord pr on  pr.TaskCode=mpc.taskCode and pr.PatientOrder=mpc.PatientOrder   --修改一车多人时收费翻倍的情况，2017-10-23 周志猛
                  left join #temp t on mpc.TaskCode = t.TaskCode and mpc.PatientOrder = t.PatientOrder
	              where dr.ID='" + id + "' ");
            sb.Append(@"drop table #temp ");

            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                D_DriverReport info = new D_DriverReport();
                if (dr.Read())
                {
                    info.PRTimes = dr["人数"].ToString();
                    info.EmptyTimes = dr["空车"].ToString();
                    info.InvoiceNumberTotal = dr["发票份数"].ToString();
                    info.baidufei = Convert.ToDouble(dr["摆渡费"]);
                    info.CarFee = Convert.ToDouble(dr["车费"]);
                    info.WaitingFee = Convert.ToDouble(dr["等候费"]);
                    info.EmergencyFee = Convert.ToDouble(dr["急救费"]);
                    info.DrugFeeTotal = Convert.ToDouble(dr["药品费"]);
                    info.ExamineFeeTotal = Convert.ToDouble(dr["检查费"]);
                    info.ConsumableFeeTotal = Convert.ToDouble(dr["材料费"]);
                    info.MeasureFeeTotal = Convert.ToDouble(dr["救治措施费"]);
                    info.PRFeeTotal = Convert.ToDouble(dr["治疗费"]);
                    info.ReceivableTotal = Convert.ToDouble(dr["应收合计"]);
                    info.PaidMoney = Convert.ToDouble(dr["实收合计"]);

                    info.PaidCarFee = Convert.ToDouble(dr["车费实收合计"]); //2017-11-09
                    info.PaidPRFee = Convert.ToDouble(dr["治疗费实收合计"]);

                    ////add2021-10-15 plq start
                    //info.PaidEmergencyFee = Convert.ToDouble(dr["急救费实收"]);
                    //info.PaidDrugFee = Convert.ToDouble(dr["药品费实收"]);
                    //info.PaidExamineFee = Convert.ToDouble(dr["检查费实收"]);
                    //info.PaidConsumableFee = Convert.ToDouble(dr["材料费实收"]);
                    //info.PaidMeasureFee = Convert.ToDouble(dr["救治措施费实收"]);

                    //add2021-10-15 plq 医保上线移植
                    info.XJMoney = Convert.ToDouble(dr["现金收费"]);
                    info.YBMoney = Convert.ToDouble(dr["医保收费"]);
                    info.MLMoney = Convert.ToDouble(dr["分币误差"]);

                    info.ZFBMoney = Convert.ToDouble(dr["支付宝实收"]); //add2022-01-07 plq 增加明细
                    info.WXMoney = Convert.ToDouble(dr["微信实收"]);
                    info.YLMoney = Convert.ToDouble(dr["银联收费"]);

                }
                return info;
            }
        }

        //取收费发票相关信息   
        public D_DriverReport GetFPLink(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"  select distinct 发票号=stuff((select ','+substring(mpc.InvoiceNumber,1,10) from M_PatientCharge  mpc  
	                       left join D_DriverReportPick drp on  mpc.TaskCode=drp.taskCode
	                       where drp.ReportID='" + id + @"' order by substring(mpc.InvoiceNumber,1,10)  for xml path('')),1,1,'')
	                      ,重打发票号=isnull(stuff((select ','+substring(mpcl.InvoiceNumber,1,10) from M_PatientChargeLog  mpcl 
	                       left join D_DriverReportPick drp on  mpcl.TaskCode=drp.taskCode
	                       where drp.ReportID='" + id + @"' and mpcl.Isvalid=0  order by substring(mpcl.InvoiceNumber,1,10)  for xml path('')),1,1,''),'')
                           ,空车中止发票号=isnull(stuff((select ','+substring(mpc.InvoiceNumber,1,10) from M_PatientCharge  mpc
                            left join D_DriverReportPick drp on  mpc.TaskCode=drp.taskCode
                            where drp.ReportID='" + id + @"' and mpc.ReceivableTotal=0 and mpc.PaidMoney=0  order by substring(mpcl.InvoiceNumber,1,10)  for xml path('')),1,1,''),'')
	                       from M_PatientCharge mpc
	                       left join M_PatientChargeLog mpcl on mpcl.TaskCode = mpc.TaskCode
	                       left join D_DriverReportPick drp on  drp.taskCode=mpc.TaskCode  ");
            sb.Append(" where drp.ReportID='" + id + "'  ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                D_DriverReport info = new D_DriverReport();
                if (dr.Read())
                {
                    info.InvoiceNumberString = dr["发票号"].ToString();
                    info.UselessInvoiceString = dr["重打发票号"].ToString();
                    info.EmptyInvoiceString = dr["空车中止发票号"].ToString();
                }
                return info;
            }
        }

        //获取司机
        public object Get_Driver(string id)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct 司机=case when Driver is not null and Driver<>'' then Driver+'('+DriverWorkID+')' else '' end
                        from D_DriverReportPick drp
                        where drp.ReportID='" + id + "' ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["司机"].ToString();
                    info.Name = dr["司机"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取医生
        public object Get_Doctor(string id)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct 医生=case when Doctor is not null and Doctor<>'' then Doctor+'('+DoctorWorkID+')' else '' end
                        from D_DriverReportPick drp
                        where drp.ReportID='" + id + "' ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["医生"].ToString();
                    info.Name = dr["医生"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取护士
        public object Get_Nurse(string id)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct 护士=case when Nurse is not null and Nurse<>'' then Nurse+'('+NurseWorkID+')' else '' end
                        from D_DriverReportPick drp
                        where drp.ReportID='" + id + "' ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["护士"].ToString();
                    info.Name = dr["护士"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        //获取担架员
        public object Get_Stretcher(string id)
        {
            List<CheckModelExt> list = new List<CheckModelExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct 担架员=case when Stretcher1Name is not null and Stretcher1Name<>'' then Stretcher1Name+'('+Stretcher1WorkID+')' else '' end
                        from D_DriverReportPick drp
                        where drp.ReportID='" + id + "' ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    CheckModelExt info = new CheckModelExt();
                    info.ID = dr["担架员"].ToString();
                    info.Name = dr["担架员"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
        #endregion


        #region 根据登录人工号获取所属分中心
        public string GetStationByWorkID(string workID)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append(@"select  isnull(tp.分站编码,'')  from   
                    TPerson tp  
                    where tp.工号='" + workID + "'  and tp.是否有效=1 ");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
            string list;
            if (ds.Tables[0].Rows.Count > 0)
            {
                list = Convert.ToString(ds.Tables[0].Rows[0][0]);
            }
            else { list = ""; }
            return list;
        }
        #endregion


        #region 根据工号获取昨天下班的下班路码和下班油尺
        public D_DriverReport GetoffDuty(string SAmbulance, string ReportDate)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" select   top 1   isnull(dr.OffDutyKM,0) as OffDutyKM ,isnull( dr.OffDutyDipstick,0) as OffDutyDipstick,isnull(ca.NextMaintainKM,0) as NextMaintainKM
                        from D_DriverReport dr  left join C_Ambulance ca on ca.AmbulanceCode=dr.AmbulanceCode");

            sb.Append(" where  dr.AmbulanceNumber='" + SAmbulance + "' and dr.ReportDate <='" + ReportDate + "'   and dr.FillState ='已填写日报'   order by   dr.ReportDate   desc ,dr.OffDutyKM desc  ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                D_DriverReport info = new D_DriverReport();
                if (dr.Read())
                {
                    info.OffDutyKM = Convert.ToDouble(dr["OffDutyKM"]);
                    info.OffDutyDipstick = Convert.ToDouble(dr["OffDutyDipstick"]);
                    info.NextMaintainKM = Convert.ToDouble(dr["NextMaintainKM"]);
                }
                return info;
            }
        }


        #endregion


        //用于维护日报表错误填写页面  取值
        public object GetReportMainDM(int rows, int page, ref int rowCounts, string zcenterCode, string zstationCode
            , string zPlateNumber, DateTime zReportDateStart, DateTime zReportDateEnd, string zAgentWorkID, string zMoreKM)
        {
            var q = (from a in db.D_DriverReport
                     select new
                     {
                         ID = a.ID,
                         FillState = a.FillState,
                         AgentWorkID = a.AgentWorkID,
                         AmbulanceNumber = a.AmbulanceNumber,
                         ReportDate = a.ReportDate,
                         BanCi = a.BanCi,
                         TaskTimes = a.TaskTimes,
                         LastModifyTime = a.LastModifyTime,
                         LastModifier = a.LastModifier,
                         StationName = a.StationName,
                         DriverName = a.DriverName,
                         OffDutyKM = a.OffDutyKM,
                         OnDutyKM = a.OnDutyKM,
                         DayKM = a.DayKM,
                         CenterCode = a.CenterCode,
                         StationCode = a.StationCode
                     });

            q = q.Where(a => a.ReportDate >= zReportDateStart && a.ReportDate <= zReportDateEnd);

            if (!string.IsNullOrEmpty(zcenterCode))
            {
                int centerCode = int.Parse(zcenterCode);
                q = q.Where(a => a.CenterCode == centerCode);
            }
            if (!string.IsNullOrEmpty(zstationCode))
            {
                q = q.Where(a => a.StationCode == zstationCode);
            }
            if (!string.IsNullOrEmpty(zPlateNumber))
            {
                q = q.Where(a => a.AmbulanceNumber == zPlateNumber);
            }
            if (!string.IsNullOrEmpty(zAgentWorkID))
            {
                q = q.Where(a => a.AgentWorkID == zAgentWorkID);
            }
            if (!string.IsNullOrEmpty(zMoreKM))
            {
                int MoreKM = int.Parse(zMoreKM);
                q = q.Where(a => a.DayKM >= MoreKM);
            }

            rowCounts = q.Count();
            var r = q.OrderBy(a => a.ReportDate).Skip((page - 1) * rows).Take(rows).ToList();
            return r;

        }

        //2018-12-11 zch //2021-01-07 plq 增加 现金实收、医保实收、分币误差、支付宝实收、微信实收、银联收费 六收费明细字段
        public DataTable GetDriverReportPayRecord(DateTime start, DateTime end, string stationCode, string driver, string doctor, string stretcher)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"                 
                           SELECT 
                          当班日期=ReportDate
                          ,车辆=AmbulanceNumber
                          ,分站=StationName
                          ,班次=BanCi
                          ,司机=DriverName 
                          ,医生=DoctorName
                          ,担架工=Stretcher1Name
                          ,缴款日期 = dp.PayInDate
                          ,应收总计=ReceivableTotal
                          ,实收总计=PaidMoney   
	                      ,欠费= case when  ReceivableTotal!= PaidMoney then ReceivableTotal - PaidMoney else 0 end
	                      ,已缴金额=dp.PaymentAmount
	                      ,缴费凭单号=dp.PayInNumber
	                      ,未缴金额=dp.UnPaidAmount
                          ,现金实收=isnull(XJMoney,0),医保实收=isnull(YBMoney,0),分币误差=isnull(MLMoney,0)
                          ,支付宝实收=isnull(ZFBMoney,0),微信实收=isnull(WXMoney,0),银联收费=isnull(YLMoney,0)   

                      FROM D_DriverReport dr 
                      LEFT JOIN D_DriverReprotPayIn dp on dr.ID = dp.ID   
                      WHERE dr.ReportDate >= @StartTime and dr.ReportDate <= @EndTime ");
            WhereClauseUtility.AddStringLike("dr.StationName", stationCode, sb);
            WhereClauseUtility.AddStringLike("dr.DriverName", driver, sb);
            WhereClauseUtility.AddStringLike("dr.DoctorName", doctor, sb);
            WhereClauseUtility.AddStringLike("dr.Stretcher1Name", stretcher, sb);
            sb.Append(@"                 
                         order by  dr.ReportDate  ");
            SqlParameter[] prams = new SqlParameter[] {
                new SqlParameter("@StartTime", start),   
                new SqlParameter("@EndTime", end)               
            };
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), prams);
            return ds.Tables[0];
        }

        //2018-12-11 zch
        public DataTable GetDriverReportAndInvoice(DateTime start, DateTime end, string stationCode, string driver, string doctor, string stretcher)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"                 
                            SELECT 
                           使用日期=ReportDate
						  ,发票号码 =  dr.InvoiceNumberString
						  ,发票张数=dr.InvoiceNumberTotal
						  ,空车 =   dr.EmptyInvoiceString  
						  ,中止 =''
						  ,取担架 = ''                          
						  ,欠费 = case when ReceivableTotal!= PaidMoney then ReceivableTotal - PaidMoney else 0 end
						  ,作废 =''
						  ,缴款日期 = dp.PayInDate   
						  ,应解金额=PaidMoney   
						  ,已缴金额=dp.PaymentAmount  
						  ,未缴金额=dp.UnPaidAmount    
						  ,缴费凭单号=dp.PayInNumber  						            
						  ,应收总计=ReceivableTotal
                          ,司机=DriverName 
                          ,医生=DoctorName
                          ,担架工=Stretcher1Name

                      FROM D_DriverReport dr 
                      LEFT JOIN D_DriverReprotPayIn dp on dr.ID = dp.ID   

                      WHERE dr.ReportDate >= @StartTime and dr.ReportDate <= @EndTime ");
            WhereClauseUtility.AddStringLike("dr.StationName", stationCode, sb);
            WhereClauseUtility.AddStringLike("dr.DriverName", driver, sb);
            WhereClauseUtility.AddStringLike("dr.DoctorName", doctor, sb);
            WhereClauseUtility.AddStringLike("dr.Stretcher1Name", stretcher, sb);
            SqlParameter[] prams = new SqlParameter[] {
                new SqlParameter("@StartTime", start),   
                new SqlParameter("@EndTime", end)               
            };
            sb.Append("	order by ReportDate    ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), prams);
            return ds.Tables[0];
        }


        //2019-06-17 plq 获取解款记录考勤表统计数据 
        public DataTable Get_TJ_JKJLKQ(DateTime start, DateTime end, string driver, string doctor, string stretcher, string mark)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"                 
                          SELECT 
                           ReportDate=convert(varchar(10),dr.ReportDate,21)
                          ,dr.DoctorName
                          ,dr.Stretcher1Name
                          ,dr.DriverName 
                          ,dr.BanCi
                          ,dp.AmountPayable
                          ,dp.PaymentAmount
                          ,dp.PayInNumber
	                      ,PayInDate=convert(varchar(10),dp.PayInDate,21)
						  ,WorkDate=convert(varchar(10),dr.WorkDate,21)
	                      ,SubmitDate=convert(varchar(10),dp.SubmitDate,21)
	                      ,dp.Remark
                          ,CarRemark=dr.Remark      --add20191130 plq 添加出车备注字段
						  ,Mark=case when dr.WorkDate is not null and dp.PayInDate is not null and dr.WorkDate <>'' and dp.PayInDate <>'' 
									 then ( case when  dr.BanCi= '8小时' then (case when DATEDIFF(day,dr.WorkDate,dp.PayInDate)<=2 then '未超期' else '超期' end) 
									             when  dr.BanCi= '12小时日班' then (case when DATEDIFF(day,dr.WorkDate,dp.PayInDate)<=2 then '未超期' else '超期' end) 
									             when  dr.BanCi= '12小时夜班' then (case when DATEDIFF(day,dr.WorkDate,dp.PayInDate)<=1 then '未超期' else '超期' end) 
									             when  dr.BanCi= '24小时班' then (case when DATEDIFF(day,dr.WorkDate,dp.PayInDate)<=2 then '未超期' else '超期' end) 
									             else '班次不在列表内' end )
								     else '' end
                      into tempA
                      FROM D_DriverReprotPayIn dp 
                      LEFT JOIN D_DriverReport dr on dr.ID = dp.ID  
                      WHERE 1=1 ");
            sb.Append(@"
                     select * from tempA where 1=1  ");
            WhereClauseUtility.AddDateTimeGreaterThan("ReportDate", start, sb);
            WhereClauseUtility.AddDateTimeLessThan("ReportDate", end, sb);
            WhereClauseUtility.AddStringLike("DriverName", driver, sb);
            WhereClauseUtility.AddStringLike("DoctorName", doctor, sb);
            WhereClauseUtility.AddStringLike("Stretcher1Name", stretcher, sb);
            WhereClauseUtility.AddStringEqual("Mark", mark, sb);
            sb.Append("	order by ReportDate  ");
            sb.Append(" drop table tempA  ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];
        }

        #region 移动支付--银联对账 add2022-01-13 plq 从闵行移植

        #region 分中心，分站，分站流水对账  yh 20180111 新对账

        //获取分中心对账合计
        public object GetCenterChargeCheckNew(ref int rowtotal, DateTime start, DateTime end)
        {
            StringBuilder sb = new StringBuilder();
            string tradeNoS = start.ToString("yyyyMMddHHmmss") + "000000";
            string tradeNoE = end.ToString("yyyyMMddHHmmss") + "000000";
            sb.Append(@"
                    select 商户订单号,实收金额 
                    into #temp1  
                    from I_PayDetailEXCEL 
                    where  商户订单号>='" + tradeNoS + "' and 商户订单号<='" + tradeNoE + "' ");
            //if (paytype == 2)
            //    sb.Append(" and 状态='买家已支付'");
            //if (paytype == 1)
            //    sb.Append(" and 订单金额 != 0 ");//过滤掉提现记录 20180125
            sb.Append(@"   
                    select  分中心=tc.名称,分中心编码=tc.编码,分站=ts.名称,分站编码=ts.编码,收费时间=mpc.Date,发票号=mpc.InvoiceNumber,                          
                            司机=mpr.Driver,医护=mpr.DoctorAndNurse,担架员=mpr.StretcherBearersI,病人姓名=mpc.PatientName,
                            支付方式=case when mppr.支付类型='pay.alipay.jspay' then '支付宝' when mppr.支付类型='pay.weixin.jspay' then '微信' end,
                            商户订单号=mppr.订单号,任务编码=mppr.任务编码,
                            是否审核=case when mpc.IFAccountCheck=1 then '已审核' else '' end
                            ,isnull(CarFee,0) as 车费
                            ,isnull(WaitingFee,0)  as 等候费
                            ,isnull(EmergencyFee,0) as 急救费
                            ,isnull(DrugFeeTotal,0) as 药费
                            ,isnull(ExamineFeeTotal,0) as 检查费
                            ,isnull(ConsumableFeeTotal,0) as 材料费
                            ,isnull(MeasureFeeTotal,0) as 治疗费
                            ,(isnull(EmergencyFee,0)+ isnull(DrugFeeTotal,0)+ isnull(ExamineFeeTotal,0)+ isnull(ConsumableFeeTotal,0)+ isnull(MeasureFeeTotal,0)) as 医疗费
                            ,isnull(ReceivableTotal,0) as 应收
                            ,isnull(mppr.交易金额,0) as 实收
                            ,isnull(PaidCarFee,0) as 车费实收
                            ,isnull(PaidPRFee,0) as 医疗费实收                                
                    into #temp2                             
                    from M_PatientPayRecord mppr
                    left join M_PatientCharge mpc on mpc.TaskCode=mppr.任务编码 and mppr.病历序号 = mpc.PatientOrder
                    left join M_PatientRecord mpr on mpr.TaskCode=mpc.TaskCode and  mpc.PatientOrder = mpr.PatientOrder");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TTask tt on tt.任务编码=mppr.任务编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=tt.分站编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=ts.中心编码 ");
            sb.Append(" where  mppr.订单号>='" + tradeNoS + "' and mppr.订单号<='" + tradeNoE + "' and mppr.交易结果=1 ");
            //WhereClauseUtility.AddIntEqual("mppr.支付类型", paytype, sb);
            sb.Append(@"select 分中心=t2.分中心,分中心编码=t2.分中心编码,应收合计=sum(isnull(t2.应收,0)),实收合计=sum(isnull(t2.实收,0))
					           ,审核=sum(case when t2.是否审核='已审核' then 1 else 0 end),任务编码=count(t2.任务编码),支付宝或微信合计=sum(isnull(t1.实收金额,0))
			            from #temp1 t1
			            full join #temp2 t2 on t1.商户订单号=t2.商户订单号
			            group by t2.分中心,t2.分中心编码 
                        drop table #temp1,#temp2 ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            List<TaskChargeDetailInfo> list = new List<TaskChargeDetailInfo>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                TaskChargeDetailInfo info = new TaskChargeDetailInfo();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new TaskChargeDetailInfo();
                    info.CenterCode = Convert.ToString(dr["分中心编码"]);
                    info.CenterName = Convert.ToString(dr["分中心"]);
                    info.ReceivableTotal = Convert.ToDecimal(dr["应收合计"]);
                    info.PaidMoney = Convert.ToDecimal(dr["实收合计"]);
                    info.RecordMoney = Convert.ToDecimal(dr["支付宝或微信合计"]);
                    info.IFAccountCheck = Convert.ToString(dr["审核"]); //已审核 数量
                    info.ID = Convert.ToString(dr["任务编码"]); //任务编码 数量
                    list.Add(info);
                }
                TaskChargeDetailInfo dinfo = new TaskChargeDetailInfo();
                if (list.Count > 0)
                {
                    dinfo.CenterName = "合计";
                    dinfo.ReceivableTotal = list.Sum(p => p.ReceivableTotal);
                    dinfo.PaidMoney = list.Sum(p => p.PaidMoney);
                    dinfo.RecordMoney = list.Sum(p => p.RecordMoney);
                }
                else
                {
                    dinfo.ReceivableTotal = 0;
                    dinfo.PaidMoney = 0;
                    dinfo.RecordMoney = 0;
                }
                list.Add(dinfo);
            }
            else
            {
                list = null;
                rowtotal = 0;
                return list;
            }
            rowtotal = Convert.ToInt32(ds.Tables[0].Rows.Count);
            return list;
        }

        //获取分站对账汇总 paytype =1 支付宝，paytype=2 微信
        public object GetStationChargeCheckNew(ref int rowtotal, DateTime start, DateTime end, string centerCode)
        {
            StringBuilder sb = new StringBuilder();
            string tradeNoS = start.ToString("yyyyMMddHHmmss") + "000000";
            string tradeNoE = end.ToString("yyyyMMddHHmmss") + "000000";
            sb.Append(@"
                    select 商户订单号,实收金额 
                    into #temp1  
                    from I_PayDetailEXCEL 
                    where  商户订单号>='" + tradeNoS + "' and 商户订单号<='" + tradeNoE + "'");
            sb.Append(@"   
                    select  分中心=tc.名称,分中心编码=tc.编码,分站=ts.名称,分站编码=ts.编码,收费时间=mpc.Date,发票号=mpc.InvoiceNumber,                          
                            司机=mpr.Driver,医护=mpr.DoctorAndNurse,担架员=mpr.StretcherBearersI,病人姓名=mpc.PatientName,
                            支付方式=case when mppr.支付类型='pay.alipay.jspay' then '支付宝'when mppr.支付类型='pay.weixin.jspay' then '微信' end,
                            商户订单号=mppr.订单号,任务编码=mppr.任务编码,
                            是否审核=case when mpc.IFAccountCheck=1 then '已审核' else '' end
                            ,isnull(CarFee,0) as 车费
                            ,isnull(WaitingFee,0)  as 等候费
                            ,isnull(EmergencyFee,0) as 急救费
                            ,isnull(DrugFeeTotal,0) as 药费
                            ,isnull(ExamineFeeTotal,0) as 检查费
                            ,isnull(ConsumableFeeTotal,0) as 材料费
                            ,isnull(MeasureFeeTotal,0) as 治疗费
                            ,(isnull(EmergencyFee,0)+ isnull(DrugFeeTotal,0)+ isnull(ExamineFeeTotal,0)+ isnull(ConsumableFeeTotal,0)+ isnull(MeasureFeeTotal,0)) as 医疗费
                            ,isnull(ReceivableTotal,0) as 应收
                            ,isnull(mppr.交易金额,0) as 实收
                            ,isnull(PaidCarFee,0) as 车费实收
                            ,isnull(PaidPRFee,0) as 医疗费实收                                
                    into #temp2                             
                    from M_PatientPayRecord mppr
                    left join M_PatientCharge mpc on mpc.TaskCode=mppr.任务编码 and mppr.病历序号 = mpc.PatientOrder
                    left join M_PatientRecord mpr on mpr.TaskCode=mpc.TaskCode and  mpc.PatientOrder = mpr.PatientOrder ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TTask tt on tt.任务编码=mppr.任务编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=tt.分站编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=ts.中心编码 ");
            sb.Append(" where  mppr.订单号>='" + tradeNoS + "' and mppr.订单号<='" + tradeNoE + "' and mppr.交易结果=1 ");
            //WhereClauseUtility.AddIntEqual("mppr.支付类型", paytype, sb);
            sb.Append(@"select 分站编码=t2.分站编码,分站=t2.分站,应收合计=sum(isnull(t2.应收,0)),实收合计=sum(isnull(t2.实收,0)),
					           审核=sum(case when t2.是否审核='已审核' then 1 else 0 end),任务编码 =count(t2.任务编码) ,支付宝或微信合计=sum(isnull(t1.实收金额,0))
			            from #temp1 t1
			            full join #temp2 t2 on t1.商户订单号=t2.商户订单号 ");
            if (centerCode == null || centerCode == "")
                sb.Append(" where t2.分中心编码 is null ");
            else
                sb.Append(" where t2.分中心编码= '" + centerCode + "'");
            sb.Append(@"group by t2.分站,t2.分站编码 
                        drop table #temp1,#temp2 ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            List<TaskChargeDetailInfo> list = new List<TaskChargeDetailInfo>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                TaskChargeDetailInfo info = new TaskChargeDetailInfo();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new TaskChargeDetailInfo();
                    info.StationCode = Convert.ToString(dr["分站编码"]);
                    info.StationName = Convert.ToString(dr["分站"]);
                    info.ReceivableTotal = Convert.ToDecimal(dr["应收合计"]);
                    info.PaidMoney = Convert.ToDecimal(dr["实收合计"]);
                    info.RecordMoney = Convert.ToDecimal(dr["支付宝或微信合计"]);
                    info.IFAccountCheck = Convert.ToString(dr["审核"]); //已审核 数量
                    info.ID = Convert.ToString(dr["任务编码"]); //任务编码 数量
                    list.Add(info);
                }

                TaskChargeDetailInfo dinfo = new TaskChargeDetailInfo();
                if (list.Count > 0)
                {
                    dinfo.StationName = "合计";
                    dinfo.ReceivableTotal = list.Sum(p => p.ReceivableTotal);
                    dinfo.PaidMoney = list.Sum(p => p.PaidMoney);
                    dinfo.RecordMoney = list.Sum(p => p.RecordMoney);
                }
                else
                {
                    dinfo.ReceivableTotal = 0;
                    dinfo.PaidMoney = 0;
                    dinfo.RecordMoney = 0;
                }
                list.Add(dinfo);
            }
            else
            {
                list = null;
                rowtotal = 0;
                return list;
            }
            rowtotal = Convert.ToInt32(ds.Tables[0].Rows.Count);
            return list;
        }


        //取任务详细信息 
        public object GetTaskChargeCheckNew(ref int rowtotal, ref List<TaskChargeDetailInfo> footList, DateTime start, DateTime end, string station, string ifCheck)
        {
            StringBuilder sb = new StringBuilder();
            string tradeNoS = start.ToString("yyyyMMddHHmmss") + "000000";
            string tradeNoE = end.ToString("yyyyMMddHHmmss") + "000000";
            sb.Append(@"
                    select 商户订单号,实收金额=isnull(实收金额,0) 
                    into #temp1  
                    from I_PayDetailEXCEL 
                    where 商户订单号>='" + tradeNoS + "' and 商户订单号<='" + tradeNoE + "'");
            sb.Append(@"   
                    select  分中心=tc.名称,分中心编码=tc.编码,分站=ts.名称,分站编码=ts.编码,收费时间=mpc.Date,发票号=mpc.InvoiceNumber,                          
                            司机=mpr.Driver,医护=mpr.DoctorAndNurse,担架员=mpr.StretcherBearersI,病人姓名=mpc.PatientName,
                            支付方式=case when mppr.支付类型='pay.alipay.jspay' then '支付宝'when mppr.支付类型='pay.weixin.jspay' then '微信' end,
                            商户订单号=mppr.订单号,任务编码=mppr.任务编码,
                            是否审核=case when mpc.IFAccountCheck=1 then '已审核' else '' end
                            ,isnull(CarFee,0) as 车费
                            ,isnull(WaitingFee,0)  as 等候费
                            ,isnull(EmergencyFee,0) as 急救费
                            ,isnull(DrugFeeTotal,0) as 药费
                            ,isnull(ExamineFeeTotal,0) as 检查费
                            ,isnull(ConsumableFeeTotal,0) as 材料费
                            ,isnull(MeasureFeeTotal,0) as 治疗费
                            ,(isnull(EmergencyFee,0)+ isnull(DrugFeeTotal,0)+ isnull(ExamineFeeTotal,0)+ isnull(ConsumableFeeTotal,0)+ isnull(MeasureFeeTotal,0)) as 医疗费
                            ,isnull(ReceivableTotal,0) as 应收
                            ,isnull(mppr.交易金额,0) as 实收
                            ,isnull(PaidCarFee,0) as 车费实收
                            ,isnull(PaidPRFee,0) as 医疗费实收                                
                    into #temp2                             
                    from M_PatientPayRecord mppr
                    left join M_PatientCharge mpc on mpc.TaskCode=mppr.任务编码 and mppr.病历序号 = mpc.PatientOrder
                    left join M_PatientRecord mpr on mpr.TaskCode=mpc.TaskCode  and  mpc.PatientOrder = mpr.PatientOrder");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TTask tt on tt.任务编码=mppr.任务编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=tt.分站编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=ts.中心编码 ");
            sb.Append(" where  mppr.订单号>='" + tradeNoS + "' and mppr.订单号<='" + tradeNoE + "' and mppr.交易结果=1 ");
            // WhereClauseUtility.AddIntEqual("mppr.支付类型", paytype, sb);
            sb.Append(@"select 分中心=t2.分中心,分站=t2.分站,任务编码=t2.任务编码,t2.病人姓名,t2.材料费,t2.车费,
                               t2.车费实收,t2.担架员,t2.等候费,t2.发票号,t2.急救费,t2.检查费,
                               t2.商户订单号,实收=isnull(t2.实收,0),t2.是否审核,t2.收费时间,t2.司机,t2.药费,t2.医护,t2.医疗费,
                               t2.医疗费实收,应收=isnull(t2.应收,0),t2.支付方式,t2.治疗费,
                               支付宝或微信实收=isnull(t1.实收金额,0),订单号=t1.商户订单号
			            from #temp1 t1
			            full join #temp2 t2 on t1.商户订单号=t2.商户订单号 where 1=1 ");
            if (station == null || station == "")
                sb.Append(" and t2.分站编码 is null ");
            else
                sb.Append(" and t2.分站编码='" + station + "' ");
            if (ifCheck == "true")
                sb.Append(" and t2.是否审核='已审核'");
            if (ifCheck == "false")
                sb.Append(" and t2.是否审核=''");
            sb.Append(@" drop table #temp1,#temp2 ");
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            List<TaskChargeDetailInfo> list = new List<TaskChargeDetailInfo>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                TaskChargeDetailInfo info = new TaskChargeDetailInfo();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new TaskChargeDetailInfo();
                    info.CenterName = Convert.ToString(dr["分中心"]);
                    info.StationName = Convert.ToString(dr["分站"]);
                    info.ID = Convert.ToString(dr["任务编码"]);
                    info.ChargeTime = Convert.ToString(dr["收费时间"]);
                    info.InvoiceNumber = Convert.ToString(dr["发票号"]);
                    info.PatientName = Convert.ToString(dr["病人姓名"]);
                    info.Driver = Convert.ToString(dr["司机"]);
                    info.DoctorAndNurse = Convert.ToString(dr["医护"]);
                    info.Stretcher = Convert.ToString(dr["担架员"]);
                    info.CarFee = Convert.ToString(dr["车费"]);
                    info.WaitingFee = Convert.ToString(dr["等候费"]);
                    info.EmergencyFee = Convert.ToString(dr["急救费"]);
                    info.DrugFeeTotal = Convert.ToString(dr["药费"]);
                    info.ExamineFeeTotal = Convert.ToString(dr["检查费"]);
                    info.ConsumableFeeTotal = Convert.ToString(dr["材料费"]);
                    info.MeasureFeeTotal = Convert.ToString(dr["治疗费"]);
                    info.PRFee = Convert.ToString(dr["医疗费"]);
                    info.ReceivableTotal = Convert.ToDecimal(dr["应收"]);
                    info.PaidCarFee = Convert.ToString(dr["车费实收"]);
                    info.PaidPRFee = Convert.ToString(dr["医疗费实收"]);
                    info.PaidMoney = Convert.ToDecimal(dr["实收"]);
                    info.ChargeType = Convert.ToString(dr["支付方式"]);
                    info.OutTradeNo = Convert.ToString(dr["订单号"]);
                    info.IFAccountCheck = Convert.ToString(dr["是否审核"]);
                    info.RecordMoney = Convert.ToDecimal(dr["支付宝或微信实收"]);
                    list.Add(info);
                }

                TaskChargeDetailInfo dinfo = new TaskChargeDetailInfo();
                if (list.Count > 0)
                {
                    dinfo.StationName = "合计";
                    dinfo.ReceivableTotal = list.Sum(p => p.ReceivableTotal);
                    dinfo.PaidMoney = list.Sum(p => p.PaidMoney);
                    dinfo.RecordMoney = list.Sum(p => p.RecordMoney);
                }
                else
                {
                    dinfo.ReceivableTotal = 0;
                    dinfo.PaidMoney = 0;
                    dinfo.RecordMoney = 0;
                }
                footList.Add(dinfo);
            }
            else
            {
                // list = null;
                rowtotal = 0;
                footList = null;
                return list;

            }
            rowtotal = Convert.ToInt32(ds.Tables[0].Rows.Count);
            return list;
        }

        //自定义对账
        public object GetTaskChargeDetailNew(int rows, int page, ref int rowtotal, ref List<TaskChargeDetailInfo> footList, DateTime start, DateTime end, int center, string station, int ifcheck, string invoicenumber, string outtradeno, string patientname)
        {
            StringBuilder sb = new StringBuilder();
            string tradeNoS = start.ToString("yyyyMMddHHmmss") + "000000";
            string tradeNoE = end.ToString("yyyyMMddHHmmss") + "000000";
            sb.Append(@"
                    select 商户订单号,实收金额=isnull(实收金额,0)
                    into #temp1  
                    from I_PayDetailEXCEL 
                    where  商户订单号>='" + tradeNoS + "' and 商户订单号<='" + tradeNoE + "'");
            sb.Append(@"   
                    select  分中心=tc.名称,分中心编码=tc.编码,分站=ts.名称,分站编码=ts.编码,收费时间=mpc.Date,发票号=mpc.InvoiceNumber,                          
                            司机=mpr.Driver,医护=mpr.DoctorAndNurse,担架员=mpr.StretcherBearersI,病人姓名=mpc.PatientName,
                            支付方式=case when mppr.支付类型='pay.alipay.jspay' then '支付宝'when mppr.支付类型='pay.weixin.jspay' then '微信' end,
                            商户订单号=mppr.订单号,任务编码=mppr.任务编码,
                            是否审核=case when mpc.IFAccountCheck=1 then '已审核' else '' end
                            ,isnull(CarFee,0) as 车费
                            ,isnull(WaitingFee,0)  as 等候费
                            ,isnull(EmergencyFee,0) as 急救费
                            ,isnull(DrugFeeTotal,0) as 药费
                            ,isnull(ExamineFeeTotal,0) as 检查费
                            ,isnull(ConsumableFeeTotal,0) as 材料费
                            ,isnull(MeasureFeeTotal,0) as 治疗费
                            ,(isnull(EmergencyFee,0)+ isnull(DrugFeeTotal,0)+ isnull(ExamineFeeTotal,0)+ isnull(ConsumableFeeTotal,0)+ isnull(MeasureFeeTotal,0)) as 医疗费
                            ,isnull(ReceivableTotal,0) as 应收
                            ,isnull(mppr.交易金额,0) as 实收
                            ,isnull(PaidCarFee,0) as 车费实收
                            ,isnull(PaidPRFee,0) as 医疗费实收                                
                    into #temp2                             
                    from M_PatientPayRecord mppr
                    left join M_PatientCharge mpc on mpc.TaskCode=mppr.任务编码 and mppr.病历序号 = mpc.PatientOrder
                    left join M_PatientRecord mpr on mpr.TaskCode=mpc.TaskCode and  mpc.PatientOrder = mpr.PatientOrder ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TTask tt on tt.任务编码=mppr.任务编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=tt.分站编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=ts.中心编码 ");
            sb.Append(" where  mppr.订单号>='" + tradeNoS + "' and mppr.订单号<='" + tradeNoE + "' and mppr.交易结果=1 ");
            //WhereClauseUtility.AddIntEqual("mppr.支付类型", paytype, sb);
            sb.Append(" order by mppr.订单号 ");
            sb.Append(@"select 分中心=t2.分中心,分站=t2.分站,任务编码=t2.任务编码,t2.病人姓名,t2.材料费,t2.车费,
                               t2.车费实收,t2.担架员,t2.等候费,t2.发票号,t2.急救费,t2.检查费,
                               t2.商户订单号,实收=isnull(t2.实收,0),t2.是否审核,t2.收费时间,t2.司机,t2.药费,t2.医护,t2.医疗费,
                               t2.医疗费实收,应收=isnull(t2.应收,0),t2.支付方式,t2.治疗费,
                               支付宝或微信实收=isnull(t1.实收金额,0),订单号=t1.商户订单号
                        --into #temp
			            from #temp1 t1
			            full join #temp2 t2 on t1.商户订单号=t2.商户订单号 where 1=1 ");
            if (ifcheck == 1)
                sb.Append(" and t2.是否审核='已审核'");
            if (ifcheck == 0)
                sb.Append(" and t2.是否审核=''");
            sb.Append(" and (t2.商户订单号 like '%" + outtradeno + "%' or t1.商户订单号 like '%" + outtradeno + "%') ");
            WhereClauseUtility.AddIntEqual("t2.分中心编码", center, sb);
            WhereClauseUtility.AddStringEqual("t2.分站编码", station, sb);
            WhereClauseUtility.AddStringLike("t2.发票号", invoicenumber, sb);
            WhereClauseUtility.AddStringLike("t2.病人姓名", patientname, sb);
            sb.Append(@" drop table #temp1,#temp2");

            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            List<TaskChargeDetailInfo> list = new List<TaskChargeDetailInfo>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                TaskChargeDetailInfo info = new TaskChargeDetailInfo();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    info = new TaskChargeDetailInfo();
                    info.CenterName = Convert.ToString(dr["分中心"]);
                    info.StationName = Convert.ToString(dr["分站"]);
                    info.ID = Convert.ToString(dr["任务编码"]);
                    info.ChargeTime = Convert.ToString(dr["收费时间"]);
                    info.InvoiceNumber = Convert.ToString(dr["发票号"]);
                    info.PatientName = Convert.ToString(dr["病人姓名"]);
                    info.Driver = Convert.ToString(dr["司机"]);
                    info.DoctorAndNurse = Convert.ToString(dr["医护"]);
                    info.Stretcher = Convert.ToString(dr["担架员"]);
                    info.CarFee = Convert.ToString(dr["车费"]);
                    info.WaitingFee = Convert.ToString(dr["等候费"]);
                    info.EmergencyFee = Convert.ToString(dr["急救费"]);
                    info.DrugFeeTotal = Convert.ToString(dr["药费"]);
                    info.ExamineFeeTotal = Convert.ToString(dr["检查费"]);
                    info.ConsumableFeeTotal = Convert.ToString(dr["材料费"]);
                    info.MeasureFeeTotal = Convert.ToString(dr["治疗费"]);
                    info.PRFee = Convert.ToString(dr["医疗费"]);
                    info.ReceivableTotal = Convert.ToDecimal(dr["应收"]);
                    info.PaidCarFee = Convert.ToString(dr["车费实收"]);
                    info.PaidPRFee = Convert.ToString(dr["医疗费实收"]);
                    info.PaidMoney = Convert.ToDecimal(dr["实收"]);
                    info.ChargeType = Convert.ToString(dr["支付方式"]);
                    info.OutTradeNo = Convert.ToString(dr["订单号"]);
                    info.IFAccountCheck = Convert.ToString(dr["是否审核"]);
                    info.RecordMoney = Convert.ToDecimal(dr["支付宝或微信实收"]);
                    list.Add(info);
                }

                TaskChargeDetailInfo dinfo = new TaskChargeDetailInfo();
                if (list.Count > 0)
                {
                    dinfo.StationName = "合计";
                    dinfo.ReceivableTotal = list.Sum(p => p.ReceivableTotal);
                    dinfo.PaidMoney = list.Sum(p => p.PaidMoney);
                    dinfo.RecordMoney = list.Sum(p => p.RecordMoney);
                }
                else
                {
                    dinfo.ReceivableTotal = 0;
                    dinfo.PaidMoney = 0;
                    dinfo.RecordMoney = 0;
                }
                footList.Add(dinfo);
            }
            else
            {
                list = null;
                rowtotal = 0;
                footList = null;
                return list;
            }
            rowtotal = Convert.ToInt32(ds.Tables[0].Rows.Count);
            //rowtotal = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return list;
        }
        #endregion

        //财务单个审核
        public int UpdateChargeAccountCheck(string taskcode)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update M_PatientCharge set IFAccountCheck=1  where TaskCode='" + taskcode + "'");
            int result = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return result;
        }

        //财务单个审核撤销
        public int CancelChargeAccountCheck(string taskcode)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("update M_PatientCharge set IFAccountCheck=0  where TaskCode='" + taskcode + "'");
            int result = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return result;
        }

        #endregion

    }
}